Release 10.1A: OpenEdge Data Management:
DataServer for Microsoft SQL Server

Data source record locking

In a DataServer application, MS SQL Server handles all of its own locking issues. The Progress 4GL locking rules are modified when you access information from an MSS data source. As a result, the Progress phrases NO–LOCK and SHARE–LOCK have isolation-level dependencies. The EXCLUSIVE-LOCK behaves the same in MS SQL Server as in an OpenEdge database.

Table 2–5 provides data source specific comparisons.

Table 2–5: OpenEdge database and data source locking 

OpenEdge	Data source
NO–LOCK	Can support the NO–LOCK option in a manner consistent with the OpenEdge database when transaction isolation level is set to read uncommitted.
SHARE–LOCK	Can support shared locks at the table, page, and record level. However, the scope and duration of the OpenEdge database vs. MS SQL Server shared locks can differ depending on how data source cursors behave at a transaction boundary and how isolation levels are set. The repeatable read isolation level emulates the OpenEdge database SHARE-LOCK behavior most closely. For more information, see your MS SQL Server documentation.
EXCLUSIVE–LOCK	Can support the EXCLUSIVE-LOCK option in a manner consistent with the OpenEdge database using any available isolation level. However, the MS SQL Server optimizer might produce locks at either the table, page, or the record level.

The DataDirect drivers provide four transaction isolation levels in the following order from least to most restrictive: read uncommitted, read committed, repeatable read, and serializable. In a multi-user configuration, you can isolate users from each other in your data source by setting the isolation level. In your OpenEdge schema holder, use the –Dsrv TXN_ISOLATION,n connection parameter (where n = 1, 2, 4, or 8) to set the isolation level in ODBC. See Microsoft documentation and the MS SQL Server documentation for more information.

Note: MS SQL Server might use page-level or table-level locking rather than record-level locking, if its optimizer determines this is the best choice. This can affect data access when two or more users attempt to read or update different records that are on the same page. See your MS SQL Server documentation for details.

Table 2–6: TXN_ISOLATION values in the –Dsrv parameter

Value	Meaning
1	Read uncommitted (default)
2	Read committed
4	Repeatable read
8	Serializable

Table 2–6 shows the possible –Dsrv TXN_ISOLATION,n values with the respective meaning.